Mega Code Archive
Select Query
1) (1=1 or 1=0) and 0=1
2) = NULL or is NULL
3) 1=1 or (1=0 and 0=1) (put and into parentheses)
4) 1=1 or 1=0 and 0=1 (no parentheses)
5) A function using the LIKE operator to return a phone numbers area code
6) A list of all Employees whose salary is more than $5000
7) A searched CASE statement
8) Add 30 seconds to current date
9) Add days on a date
10) Add hour, minute and second to a date
11) Alias name includes an embedded space
12) All attendees whose id is not in the list
13) All customers who have placed at least one order from us in the last month
14) All flower product with price range between 10 and 25
15) Alternative Quoting Mechanism in Oracle 10g
16) AND operator allows you to combine two comparisons into one
17) AND operator joins two or more conditions in one query
18) AND operator takes precedence over the OR operator
19) AND operator to join conditions
20) Any conditions based on the outcome of a group function must be in the HAVING clause
21) AS for alias
22) BETWEEN (SYSDATE-30) AND SYSDATE
23) Between And converts text value to date type value
24) Between for Date data type
25) BETWEEN operator checks whether a variable value is between two values of the same datatype
26) Born after 1960-01-01, group by department number with count() = 4
27) Born after 1965-01-01
28) Case switch based on clob data
29) Case when count() NOT between and
30) Col columnName new_value BLAH
31) Column calculation
32) Column connections for function
33) Column format
34) Column Headings for calculated column
35) Column Renaming
36) Column renaming for function
37) Column sequence in the group by impacts the ordering
38) Combine Case with group by
39) Combine case with NVL function
40) Combined columns and renaming
41) Combining WHERE Conditions
42) Comments like %0%% escape
43) Compare Date type value
44) Compare varchar type value in where clause
45) Comparison operators with character data
46) CONCAT(SUBSTR(NVL(Last_Name,xxx), 1, 3)
47) CONCATENATING TEXT
48) Concatenating columns in action
49) Convert null number column to 0
50) Convert null number value as 0
51) Convert string based date variable to date type during when comparing
52) Convert varchar value to upper case and then use like operator
53) Count all employees by evenodd employee id
54) Count distinct with group by
55) Create pattern dynamically and use it in like statement
56) Data column compare
57) Date + to_yminterval(01-05)
58) Demo for Order of Precedence for Logical Operators
59) DESC for descending
60) Description has SQL substring
61) DISTINCT
62) DISTINCT clause can be used with more than one field
63) DISTINCT operator inside a group function
64) DISTINCT tableName
65) Display the customer number and last name of all customers with no orders
66) Distinct city and state
67) Do calculation in select query
68) Eliminating Duplicate Data Using DISTINCT
69) Eliminating Duplicate Date type Data Using DISTINCT
70) Employees from new york who have gifts
71) Escape
72) Example using the MAX function with having clause
73) Exclude a range with NOT operator
74) Expression and column renaming
75) Expressions in query
76) Find all employees who are younger than employee whose id is 9999
77) Get order number by using rownum column
78) Getting the Five Most Expensive Products
79) Group and count employeem and display only if its count is more than 4
80) GROUP BY and HAVING clauses
81) Group by case
82) Group by course name then by begin date
83) GROUP BY may be used on a column without the column name appearing in the result set
84) Group joined tables
85) Grouping at Multiple Levels
86) Indicate the column names in query command
87) Insert string text between column names
88) Intersect rowid
89) Invalid relational operator
90) Is Like case sensitive
91) Is null
92) IS NULL with NOT operator
93) Joining Strings
94) Larger and equals
95) Less and equals
96) Like % function with varchar2 type
97) Like with % on both sides
98) Like with _ (Any one character)
99) Like with _ and %
100) Like with __ (Any two characters)
101) Limit the query to display only the top 3 highest paid employees
102) List all employees who are younger than Jane
103) List all employees with salary between 1300 and 1600
104) Math calculation in select
105) Minus in select
106) Must include a nonaggregate column in the SELECT list in the GROUP BY clause
107) Negate boolean expression
108) Nesting functions in action
109) NEXT_DAY(start_date-1,Tuesday)
110) Not and if statement
111) NOT BETWEEN
112) Not empty(null)
113) Not equals
114) Not has the lowerest priority
115) NOT IN processes a null in a list of values
116) Not in (1,2,3,NULL)
117) Not in vs not exists
118) NOT is a logical negation operator
119) NOT operator
120) Not(condition1 and condition2)
121) Null is null
122) Null value compare
123) NULL values on arithmetic operations
124) Number column calculation
125) OR operator
126) OR operator combines two comparisons into one
127) Or, and, not
128) Oracle converts the null into a blank text string
129) Oracle stores character data in whatever case it was inserted
130) Order by 3, 2, 1
131) ORDER BY clause with more than one column
132) Order by columns from different tables
133) Order by date value then by number value
134) Order by index
135) Order by job title ascending but birthday descending
136) Order by name and department number for salary higher than 1500
137) Order by price which is more than 50
138) Order by renamed column
139) Order by renamed column descendingly
140) Order by three columns
141) ORDER BY with all logic operators
142) Order by, range unbounded preceding
143) Order date value by only year field with extract() function
144) Order for one column descendingly
145) Order of precedence with multiple arithmetic operators
146) Order the results by the average salary
147) Order two columns
148) Ordering data in the SELECT statement ascending
149) Ordering data in the SELECT statement descending
150) Pattern Matching LIKE %great%
151) Performing Mathematics
152) Performing Range Tests with between and
153) Performing Range Tests with comparison operators
154) Performing Range Tests, not between and
155) Place a space duing the column concatenation
156) Placing parentheses around the arithmetic expression
157) Plus in select statement
158) Q{32 OHara Avenue})
159) Query a number type column with order by desc
160) Query a varchar2 type column in where statement
161) Query returning all rows and all columns from the table
162) Query specific columns with order by clause
163) Query specific columns with where clause and and operator
164) Query specific columns with where clause and or operator
165) Query specific columns with where clause, or operator and order by
166) Query the same column in where statement and order by clause
167) Query with an IN operator
168) Quoted Identifiers
169) Quoting syntax is q[ ] where the [] is the user-defined
170) Reference column name from renamed table name
171) Reference column without table name during table join
172) Reference one column more than once
173) Reference renamed column in order by
174) Referencing alias with calculation in where clause returns an error
175) Remember that the DISTINCT operator applies to the entire select list
176) Rename case when column
177) Renaming a column with a longer name
178) Retrieving the Top Five Students with ROWNUM
179) Return course name for an employee id
180) Returning a Single Column sql
181) Returning All Columns
182) Returning Multiple Columns
183) Returning Rows Call
184) ROWID as data type
185) Second letter is A
186) SELECT statement uses the not equal ( ) operator in the WHERE clause
187) Selecting Categories That Contain Product
188) Selecting Products That Belong to Category
189) Selecting Products That Belong to Department with Join
190) Set numformat 99999999999999999999 99999999999999
191) Specify ascending or descending for each column
192) Specify date in where clause
193) Start_date+TO_DSINTERVAL(100 10
194) Sub query inside having clause
195) Subqueries in a HAVING Clause
196) Test the NOT EXISTS version
197) Test the NOT IN version, but exclude the NULL
198) The % wild card also includes cases where there is no character
199) The EXISTS condition can also be combined with the NOT operator
200) The following code provides a breakdown of the basic SELECT statement on the Oracle platform
201) The optional AS keyword before the alias
202) The TO_DATE function in where clause
203) Timing and auto tracing a select statement with group
204) To find the date of the Friday after each start date
205) To_char function with alias column name
206) TO_CHAR(last_stock_date, MM-DD-YYYY HH24
207) Underscore character (_) Matches one character in a specified position
208) UNIQUE DISTINCT
209) UPPER(SUBSTR(first_name, 2, 8))
210) Use % in word ending
211) Use aggregate function in order by clause
212) Use aliases with concatenation expressions
213) Use an analytical function in a WHERE clause
214) Use Arithmetic operators between columns to derive values
215) Use Arithmetic operators in query
216) Use Arithmetic operators with literal values to derive values
217) Use avg, sum, max and count functions with group
218) Use between and to compare date data type
219) Use case when and grouping function together
220) Use case when clause to decode value
221) Use case when statement to replace if statement
222) Use case when statement with between and
223) Use case when statement with exists and subquery
224) Use case when statement with in()
225) Use case when statement with to_char() like
226) Use case when to control the order index
227) Use case when to output comments for marks
228) Use case when with comparasion operator
229) Use column command to define column name before select statement
230) Use EXISTS to link two queries
231) Use group by and avg
232) Use in operator to match three employee numbers
233) Use in operator with and in where clause
234) Use in operator with number type values
235) Use in operator with table join
236) Use in operator with varchar2 type value
237) Use or to connect two boolean expression
238) Use rownum = 1 and select into
239) Use rownum column with order by
240) Use rownum in select clause
241) Use rownum in update set statement
242) Use ROWNUM in where clause
243) Use rownum in where clause to control the row count
244) Use rownum in where clause to limit the row count
245) Use rownum to limit the subquery
246) Use sum in having clause
247) Use table alias to reference column names
248) Use table name to reference column name
249) Use table name to reference the ambiguity column names
250) Use the ALL operator in a WHERE clause to compare a value with all of the values in a list
251) Use the ANY operator in a WHERE clause to compare a value with any of the values in a list
252) Use the CASE expression and SUM function
253) Use the CASE expression and SUM function to create an order price report
254) Use Trunc in where clause
255) Use two % in Like statement
256) Use two % signs in like
257) Use two % signs in one like
258) Using a Column Multiple Times in a GROUP BY Clause
259) Using a NOT operator with like
260) Using Aliases
261) Using Aliases with Simple Subqueries
262) Using Aliasing in Equi-Joins
263) Using and, or operator in having clause
264) Using arithmetic operators with date values
265) Using avg() function in having clause
266) Using comparison operators with character data
267) Using comparison operators with expressions
268) Using Expressions
269) Using HAVING with an Analytical Function
270) Using Pattern Matching LIKE ____ %
271) Using select statement and char function to create insert statement
272) Using the BETWEEN AND operator for Number
273) Using the BETWEEN Operator for integer
274) Using the GROUP BY Clause
275) Using the HAVING Clause
276) Using the HAVING Clause and where clause
277) Using the HAVING Clause with aggregate function
278) Using the IN operator
279) Using the IS NULL operator
280) Using the operator
281) Using the ORDER BY Clause to Sort Groups
282) Using the same condition in having and where
283) Using the SUM function in HAVING Clause
284) Using the WHERE, GROUP BY, and HAVING Clauses Together
285) Using to form a long string
286) Using two single-quotes print the string correctly
287) Value pair and in operator
288) Vs =
289) Where clause converts text with date value format to date value
290) WHERE clause with a GROUP BY clause
291) Where null null
292) Where null=null (does null equal null)
293) Wild card % on both side of the string
294) With statement and subquery
295) With the simple CASE statement, you can add many more WHEN blocks
296) You could use the case statement in an SQL statement
297) You dont have to include the columns used in the GROUP BY clause in your SELECT clause